using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using DTO;


namespace DAOLayer
{
    public class MuonSachDao
    {
        public DataTable LayDanhSach()
        {
            DataTable dt = new DataTable();
            OleDbConnection cn;
            cn = DataProvider.ConnectionData();

            string strSQL;
            strSQL = "Select * From Muonsach";
            OleDbDataAdapter da;
            da = new OleDbDataAdapter(strSQL, cn);
            da.Fill(dt);

            cn.Close();
            return dt;
        }
        public void Them(MuonSachDto msDto)
        {
            OleDbConnection cn;
            cn = DataProvider.ConnectionData();

            string strSQL;
            strSQL = "Insert into Muonsach(Masach,Madocgia,Ngaymuon, Thoigianmuon) values (?, ?, ?, ?)" +
            "";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);

            cmd.Parameters.Add("@Masach", OleDbType.WChar);
            cmd.Parameters.Add("@Madocgia", OleDbType.Integer);
            cmd.Parameters.Add("@Ngaymuon", OleDbType.Date);
            cmd.Parameters.Add("@Thoigianmuon", OleDbType.Integer);

            cmd.Parameters["@Masach"].Value = msDto.MaSach;
            cmd.Parameters["@Madocgia"].Value = msDto.MaDocGia;
            cmd.Parameters["@Ngaymuon"].Value = msDto.NgayMuon;
            cmd.Parameters["@Thoigianmuon"].Value = msDto.ThoiGianMuon;

            cmd.ExecuteNonQuery();
            cn.Close();
        }
        //Tien Dung them vao
        public void Xoa(int madg)
        {
            OleDbConnection cn;
            // B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            cn = DataProvider.ConnectionData();
            // B3: Tao chuoi strSQL thao tac CSDL
            string strSQL;
            strSQL = "Delete From MuonSach Where Madocgia = ?";

            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@Madocgia", OleDbType.Integer);
            cmd.Parameters["@Madocgia"].Value = madg;
            cmd.ExecuteNonQuery();

            // B4: Dong ket noi CSDL
            cn.Close();
        }
        //Tien Dung them vao
        public IList TimKiemTheoDG(int madg)
        {
            //B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn = DataProvider.ConnectionData();

            //B3: Tao chuoi strSQL thao tac CSDL
            string strSQL = "Select * From Muonsach Where Madocgia = ?";

            //B4: Thuc thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@Madocgia", OleDbType.Integer);
            cmd.Parameters["@Madocgia"].Value = madg;
            OleDbDataReader dr = cmd.ExecuteReader();

            ArrayList dsmuonsach = new ArrayList();
            while (dr.Read())
            {
                MuonSachDto muonsach = new MuonSachDto();
                muonsach.MaSach = (int)dr["MASACH"];
                muonsach.MaDocGia = (int)dr["MADOCGIA"];
                muonsach.NgayMuon = (DateTime)dr["NGAYMUON"];
                muonsach.ThoiGianMuon = (int)dr["THOIGIANMUON"];
                dsmuonsach.Add(muonsach);
            }
            //B5: Dong ket noi CSDL
            cn.Close();
            return dsmuonsach;
        }
    }
}
